/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package javafxapplication1;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.record.CFRuleRecord;

import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.AreaReference;
import org.apache.poi.hssf.util.CellRangeAddressList;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationConstraint.ValidationType;

/**
 *
 * @author my
 */
public class ValidationWriter {

    List<String> errorList = new ArrayList<>();

    HSSFSheet sheet;
    HSSFSheet dataSheet;

    public Result write(File xls, List<ColumnInfo> cols) throws Exception {
        if (xls == null) {
            final Result result = new Result();
            result.getErrors().add("未打开任何文件");
            return result;
        }
        final POIFSFileSystem poifsFileSystem = new POIFSFileSystem(new FileInputStream(xls));
        HSSFWorkbook wb = new HSSFWorkbook(poifsFileSystem);
        sheet = wb.getSheetAt(0);
        for (int i = 0; i < cols.size(); i++) {
            try {
                final ColumnInfo c = cols.get(i);
                switch (c.getType()) {
                    case 0:
                        writeStringValidation(c, i);
                        break;
                    case 1:
                        writeRangeValidation(c, i);
                        break;
                    case 2:
                        writeSequenceValidation(c, i);
                        break;
                }
            } catch (Exception e) {
                errorList.add(e.getMessage());
            }
        }
        FileOutputStream fileOutputStream = null;
        File out = File.createTempFile("DataValidationOutFile", ".xls");
        try {
            fileOutputStream = new FileOutputStream(out);
            wb.write(fileOutputStream);
        } finally {
            if (fileOutputStream != null) {
                fileOutputStream.close();
            }
        }

        final Result result = new Result();
        result.setErrors(errorList);
        result.setOut(out);

        return result;
    }

    public void writeStringValidation(ColumnInfo c, int colNum) {

        CellRangeAddressList regions = new CellRangeAddressList(1, 60000, colNum, colNum);

        final DVConstraint createNumericConstraint = DVConstraint.createNumericConstraint(ValidationType.ANY, CFRuleRecord.ComparisonOperator.EQUAL, null, null);

        HSSFDataValidation dataValidation = new HSSFDataValidation(regions, createNumericConstraint);
        dataValidation.createPromptBox(c.getPromptTitle(), c.getPromptContent());
        sheet.addValidationData(dataValidation);
    }
/**
 * 数字区间
 * @param c
 * @param colNum 
 */
    public void writeRangeValidation(ColumnInfo c, int colNum) {
        CellRangeAddressList regions = new CellRangeAddressList(1, 60000, colNum, colNum);

        final DVConstraint createNumericConstraint = DVConstraint.createNumericConstraint(
                ValidationType.INTEGER,
                DataValidationConstraint.OperatorType.BETWEEN , 
                c.getMin(), 
                c.getMax());
      
        final HSSFDataValidation hssfDataValidation = new HSSFDataValidation(regions, createNumericConstraint);
        hssfDataValidation.createPromptBox(c.getPromptTitle(), c.getPromptContent());

        sheet.addValidationData(hssfDataValidation);
    }

    public void writeSequenceValidation(ColumnInfo c, int colNum) {

        dataSheet = sheet.getWorkbook().getSheet(DATA_SHEET);
        if (dataSheet == null) {
            dataSheet = sheet.getWorkbook().createSheet(DATA_SHEET);
        }
        //初始化序列值
        final String[] split = c.getSequence().split("\n");
        for (int i = 0; i < split.length; i++) {
            HSSFRow row = dataSheet.getRow(i);
            if (row == null) {
                row = dataSheet.createRow(i);
            }
            HSSFCell cell = row.getCell(colNum);
            if (cell == null) {
                cell = row.createCell(colNum);
            }
            cell.setCellValue(split[i]);
        }
        AreaReference ar = new AreaReference(new CellReference(0, colNum), new CellReference(split.length-1, colNum));
        String ref = "DataSheet!" + ar.formatAsString();
        //创建自定义公式,序列引用DataSheet里的单元格
        final DVConstraint createFormulaListConstraint = DVConstraint.createFormulaListConstraint(ref);
        CellRangeAddressList regions = new CellRangeAddressList(1, 60000, colNum, colNum);
        final HSSFDataValidation hssfDataValidation = new HSSFDataValidation(regions, createFormulaListConstraint);
        if (!c.getPromptContent().trim().isEmpty()) {
            hssfDataValidation.createPromptBox(c.getPromptTitle(), c.getPromptContent());
        }
        sheet.addValidationData(hssfDataValidation);
        
    }
    private static final String DATA_SHEET = "DataSheet";

}
